﻿using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel= Microsoft.Office.Interop.Excel;

namespace 节约里程法
{
    public partial class Form1 : Form
    {
        public Excel.Application excelApp;
        public Excel.Workbook workbook;
        public Excel.Worksheet worksheet;
        public Excel.Worksheet worksheet2;
        string filePath = @"D:\test\test.xlsx";
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //创建一个表格,并初始化它
            //判断是否存在
            //if (!filePath.Equals(filePath))
            //{
            //}
            excelApp = new Excel.Application();
            workbook = excelApp.Workbooks.Add();
            worksheet = (Excel.Worksheet) workbook.Worksheets[1];

            //读取节点数
            int nodeNunber;
            if (!int.TryParse(nodeNum_TextBox.Text, out nodeNunber))
            {
                MessageBox.Show("输入不合法");
                excelApp.Quit();
            }
            //int.TryParse(nodeNum_TextBox.Text,out nodeNunber);

            //写入初始化数据
            //表头
            Excel.Range range = worksheet.Range["A1","F2"];
            range.Merge();
            range.Value = "节点间运距";
            string range2_str="A"+(nodeNunber+5).ToString();
            string range2_str2 = "F" + (nodeNunber + 5).ToString();
            Excel.Range range2 = worksheet.Range[range2_str, range2_str2];
            range2.Merge();
            range2.Value = "节点运载量";
            //结构化
            worksheet.Cells[4, 1] = "P";
            for (int i = 1;i<=nodeNunber;i++)
            {
                worksheet.Cells[3,i+1] = ((char)('A'+i-1)).ToString();
                worksheet.Cells[i+4,1] = ((char)('A'+i-1)).ToString();
                worksheet.Cells[nodeNunber+6,i] = ((char)('A' + i - 1)).ToString();
            }
            worksheet.Cells[nodeNunber + 4, 1] =null;
            workbook.SaveAs(filePath);
            excelApp.Quit();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //创建节约里程表
            //表头
            excelApp=new Excel.Application();
            workbook=excelApp.Workbooks.Open(filePath);
            worksheet2 = (Excel.Worksheet)workbook.Worksheets.Add();
            Excel.Range range = worksheet2.Range["A1", "F2"];
            range.Merge();
            range.Value = "节约里程表";
            //结构化
            int nodeNunber;
            if (!int.TryParse(nodeNum_TextBox.Text, out nodeNunber))
            {
                MessageBox.Show("输入不合法");
                workbook.Close();
                excelApp.Quit();
            }
            for (int i = 1;i<=nodeNunber-1;i++)
            {
                worksheet2.Cells[3,i+1] = ((char)('A' + i)).ToString();
                worksheet2.Cells[i+3,1] = ((char)('A' + i-1)).ToString();
            }
            //计算并写入数据
            worksheet = (Excel.Worksheet)workbook.Worksheets[workbook.Worksheets.Count];
            for (int i = 2;i<=nodeNunber;i++)
            {
                for (int j = 0;j < nodeNunber - i+1; j++)
                {
                    float dataFloat = ToolClass.ReadData(worksheet, 4, i) + ToolClass.ReadData(worksheet, 4, i +j+ 1)
                    -ToolClass.ReadData(worksheet, 3 + i, i + j + 1);
                    worksheet2.Cells[2+i,i+j] = dataFloat;
                }
            }
            MessageBox.Show("over");
            workbook.Close(true);
            excelApp.Quit();
        }

        //定义一个可排序类来存储节约里程表
        public class SortClass : IComparable<SortClass>
        {
            string lineName;
            float saveMiles;

            public int CompareTo(SortClass other)
            {
                if (other==null)
                {
                    return 1;
                };
                return saveMiles.CompareTo(other.saveMiles);
            }
        }

        //定义一个静态类读取数据表
        public static class ToolClass
        {
            public static float ReadData(Worksheet worksheet,int row,int column)
            {
                try
                {
                    //读取表格数据并转换为float
                    Range cell = worksheet.Cells[row,column];
                    string cellValue=cell.Value.ToString();
                    if (float.TryParse(cellValue,out float result))
                    {
                        return result;
                    }
                    else
                    {
                        return 0;
                        throw new InvalidOperationException("输入表格中的数据不合法"+row.ToString()+column.ToString());
                    }
                }
                finally
                {

                }
            }
        }
    }
}
